#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive	

${HIVE_HOME} -S -e "
create database if not exists view_consult_dws;
use view_consult_dws;

drop table view_consult_dws.dws_view_consult;
create table view_consult_dws.dws_view_consult(
session_cnt  BIGINT comment 'session个数' ,
id_cnt       BIGINT comment '访问id个数' ,
ip_cnt       BIGINT comment '访问ip个数' ,
yearinfo         STRING COMMENT '年',
monthinfo            STRING COMMENT '月',
dayinfo      STRING COMMENT '日',
quarterinfo           STRING COMMENT '季度',
seo_source           STRING COMMENT '搜索来源',
area            STRING COMMENT '地域',
country            STRING COMMENT '所在国家',
province            STRING COMMENT '省',
city            STRING COMMENT '城市',
origin_channel            STRING COMMENT '投放渠道',
from_url                   STRING COMMENT '会话来源页面',
group_type                   STRING COMMENT '分组标记',
time_type                   STRING COMMENT '时间标记'

)comment '访问与咨询信息表'
row FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES ('orc.compress'='SNAPPY');

alter table view_consult_dws.dws_view_consult rename to dws_view;


drop table view_consult_dws.dws_consult;
create table view_consult_dws.dws_consult(
session_cnt  BIGINT comment 'session个数' ,
id_cnt       BIGINT comment '访问id个数' ,
ip_cnt       BIGINT comment '访问ip个数' ,
yearinfo         STRING COMMENT '年',
monthinfo            STRING COMMENT '月',
dayinfo      STRING COMMENT '日',
quarterinfo           STRING COMMENT '季度',
seo_source           STRING COMMENT '搜索来源',
area            STRING COMMENT '地域',
country            STRING COMMENT '所在国家',
province            STRING COMMENT '省',
city            STRING COMMENT '城市',
origin_channel            STRING COMMENT '投放渠道',
from_url                   STRING COMMENT '会话来源页面',
group_type                   STRING COMMENT '分组标记',
time_type                   STRING COMMENT '时间标记'

)comment '咨询信息表'
row FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS orc TBLPROPERTIES ('orc.compress'='SNAPPY');
"